"""
Case Type   : A兼容性-type功能增强
Case Name   : 使用抽象数据类型：创建map成员方法，plpgsql中对象比较大小测试
Create At   : 2025/5/22
Owner       : wan005
Description :
    1.创建type
    2.创建type body
    3.创建表，插入数据
    4.比较数据
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
History     :
"""
import os
import unittest
from yat.test import Node
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class Security(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'---{os.path.basename(__file__)} start---')
        self.userNode = Node(node='PrimaryDbAmode')
        self.sh_primy = CommonSH()
        self.common = Common()
        self.db_name = self.userNode.db_name
        self.type = 't_040'
        self.table = 'tb_040'

    def test_security(self):
        text = '----pre:加载A库gms插件;expect:成功----'
        self.logger.info(text)
        load_cmd = "create extension if not exists gms_output;"
        load_res = self.sh_primy.execut_db_sql(load_cmd, dbname=self.db_name)
        self.logger.info(load_res)
        self.assertIn('CREATE EXTENSION', load_res, '执行失败' + text)

        text = '----step1:创建type;expect:成功----'
        self.logger.info(text)
        sql_cmd1 = f'create or replace type {self.type} as object(name ' \
                   f'varchar2(10),birthdate date,map member function ' \
                   f'get_birdate return varchar2);'
        res1 = self.sh_primy.execut_db_sql(sql_cmd1, dbname=self.db_name)
        self.logger.info(res1)
        self.assertEqual('CREATE TYPE', res1, '执行失败' + text)

        text = '----step2:创建type body;expect:成功----'
        self.logger.info(text)
        sql_cmd2 = f'''
            create or replace type body {self.type} is
            map member function get_birdate return varchar2 is
            begin
            return trunc((sysdate-birthdate)/365);
            end;
            end;
            '''
        res2 = self.sh_primy.execut_db_sql(sql_cmd2, dbname=self.db_name)
        self.logger.info(res2)
        self.assertEqual('CREATE TYPE', res2, '执行失败' + text)

        text = '----step3:创建表，插入数据；expect:成功----'
        self.logger.info(text)
        sql_cmd3 = f'create table {self.table} (eno number(6),sal ' \
               f'number(6,2),job varchar2(20),emp4 {self.type});' \
               f'insert into {self.table}(eno,sal,job,emp4) values(0011,' \
               f'9000,\'dba\',{self.type}(\'maidi\',to_date(\'1990-12-19\',' \
               f'\'yyyy-mm-dd\')));' \
               f'insert into {self.table}(eno,sal,job,emp4) values(0022,9900,' \
               f'\'dba\',{self.type}(\'jacker\',to_date(\'1970-12-2\',' \
               f'\'yyyy-mm-dd\')));'
        res3 = self.sh_primy.execut_db_sql(sql_cmd3, dbname=self.db_name)
        self.logger.info(res3)
        self.assertEqual(2, res3.count('INSERT'), '执行失败' + text)

        text = '----step4:比较数据;expect:成功----'
        self.logger.info(text)
        sql_cmd4 = f'''
            declare
            type emp4_tab is table of {self.type};
            v_emp4_tab emp4_tab;
            v_result varchar2(100);
            begin
            select emp4 bulk collect into v_emp4_tab from {self.table};
            if v_emp4_tab(1).get_birdate>v_emp4_tab(2).get_birdate then
            v_result := v_emp4_tab(1).name ||' 比 '||v_emp4_tab(2).name ||'大';
            else
            v_result := v_emp4_tab(1).name ||' 比 '||v_emp4_tab(2).name ||'小';
            end if;
            GMS_OUTPUT.PUT_LINE(v_result);
            end;
            '''
        res4 = self.sh_primy.execut_db_sql(sql_cmd4, dbname=self.db_name)
        self.logger.info(res4)
        self.assertEqual('ANONYMOUS BLOCK EXECUTE', res4, '执行失败' + text)

    def tearDown(self):
        text = '----step1:清理环境;expect:成功----'
        cle_cmd1 = f'drop table {self.table};'
        cle_res1 = self.sh_primy.execut_db_sql(cle_cmd1, dbname=self.db_name)
        self.logger.info(cle_res1)
        self.assertEqual('DROP TABLE', cle_res1, text + '执行失败')

        cle_cmd2 = f'drop type {self.type};'
        cle_res2 = self.sh_primy.execut_db_sql(cle_cmd2, dbname=self.db_name)
        self.logger.info(cle_res2)
        self.assertEqual('DROP TYPE', cle_res2, text + '执行失败')
        self.logger.info(f'---{os.path.basename(__file__)} end---')
